#coding:utf-8
import os
import openpyxl as xlrd


class EXCEL:
    def __init__(self, file_name):
        try:
            self.__file = os.path.dirname(os.path.dirname(__file__)) + f'/DataCenter/{file_name}'
            self.excel_file = xlrd.load_workbook(self.__file)
        except:
            raise FileExistsError("文件没有找到")

    """函数的功能是获取 元素定位值和url"""

    def get_element(self, PAGE, ELEMENT_NAME, case_sheetname="元素定位"):
        global res
        try:
            res = list(self.excel_file[case_sheetname].rows)
        except:
            print(f'Worksheet{case_sheetname}不存在')
        title = [i.value for i in res[0]]
        cases = []
        for item in res[1:]:
            dataline = [i.value for i in item]
            dictline = dict(zip(title, dataline))
            cases.append(dictline)
        ele = []
        for element in cases:
            if element['PAGE'] == None:
                continue
            if element['PAGE'].strip() == PAGE and element['ELEMENT_NAME'].strip() == ELEMENT_NAME:
                if element['LOCATER_BY'].lower() == 'xpath':
                    by = 'xpath'
                elif element['LOCATER_BY'].lower() == 'id':
                    by = 'id'
                elif element['LOCATER_BY'].lower() == 'link_text':
                    by = 'link_text'
                elif element['LOCATER_BY'].lower() == 'partial_link_text':
                    by = 'partial_link_text'
                elif element['LOCATER_BY'].lower() == 'tag_name':
                    by = 'tag_name'
                elif element['LOCATER_BY'].lower() == 'class_name':
                    by = 'class_name'
                elif element['LOCATER_BY'].lower() == 'css':
                    by = 'css selector'
                elif element['LOCATER_BY'] == 'url':
                    value = element['VALUE']
                    ele.append(value)
                    return tuple(ele)
                else:
                    raise Exception(f'定位方式不存在{element["LOCATER_BY"]}')
                value = element['VALUE']
                ele.append(by)
                ele.append(value)
                return tuple(ele)
        if len(ele) == 0:
            raise Exception(f"{PAGE}页面的{ELEMENT_NAME}没有找到")
            # else:
            #     raise Exception(f'定位方式不应该为空')
            # value = element["value"]
            # return [by,value]

    """函数的功能是获取定制行列的所有 用例参数数据"""
    def get_case_data(self, line_start, line_end, col_end, case_sheetname='用例数据'):
        """如果传入的开始行号大于结束行号，胡总和列号小于0报异常"""
        if line_start>line_end or col_end<0:
            raise BaseException(f'传参存在问题 不存在line_start={line_start}>line_end={line_end} or col_end={col_end}<0')
            # print(f'传参存在问题 不存在line_start={line_start}>line_end={line_end} or col_end={col_end}<0')
        else:
            """获取通过传入的sheet名称获取sheet页"""
            case_sheet_content = self.excel_file[case_sheetname].iter_rows(min_row=line_start,max_row=line_end,min_col=1,max_col=col_end,values_only=True)
            return case_sheet_content
    """函数的功能是同坐传入的用例编号获取所有此用例列对应的断言数据"""
    def get_data_by_case_no(self,case_no,assert_sheetname="预期结果"):
        """存储所有的断言数据"""
        expect_results= []
        """因为用例编号是用文件中获取，防止存在空格，因此对传过来的数据进行空格处理"""
        case_no = case_no.strip()
        case_sheet = self.excel_file[assert_sheetname]
        case_sheet_contents = case_sheet.iter_rows(min_row=2,max_row=case_sheet.max_row,min_col=1,max_col=5,values_only=True)
        for case_sheet_content in case_sheet_contents:
            expect_resut = []
            """如果传过来的用例编号和当前从断言sheet页获取的用例编号一直，则将数据全部存储到expect_results并返回"""
            if case_sheet_content[0] == None:
                continue
            if case_no == case_sheet_content[0].strip():
                expect_results.append(case_sheet_content[1])
                expect_results.append(case_sheet_content[2])
                expect_results.append(case_sheet_content[3])
                expect_results.append(case_sheet_content[4])
                expect_results.append(expect_resut)
        return expect_results

if __name__ == '__main__':
    print(EXCEL("casesdata.xlsx").get_element("contact_search","contact"))
    print(EXCEL("casesdata.xlsx").get_data_by_case_no('login-st-002'))
    print([i for i in (EXCEL("casesdata.xlsx").get_case_data(8,8,3))])
    # print(EXCEL("casesdata.xlsx").get_data_by_case_no('预期结果','login-st-004'))